<?php

use Core\CashierController;
use Models\Money_log;
use Models\Order;
use Models\Order_item;
use Models\Refund;
use Models\Recharge;
use Models\User;
use Models\Cashier;

class StatisticController extends CashierController
{
    public function index()
    {
        $cashier_id = $this->session->cashier['id'];
        $cashier = (new Cashier)->findOrFail($cashier_id);
        $last_login = $cashier->last_login();

        $date = $this->input->get('date');
        if (!$date) {
            $date = $last_login ? date('Y-m-d', strtotime($last_login['login_time'])) : date('Y-m-d');
        }
        $begin = $date.' 00:00:00';
        $end = $date.' 23:59:59';

        $money_log = new Money_log;
        $pay_modes = [1,6,5,4,0];
        // 实际总收入
        $total = $money_log
                ->select('sum(case type when 0 then abs(num) when 1 then abs(num) else 0 end) as total_sale,
                sum(case type when 2 then abs(num) when 3 then abs(num) else 0 end) as total_recharge,
                sum(if(type=4, abs(num), 0)) as total_refund,
                sum(if(type=4, -abs(num), abs(num))) as total_income')
                ->where([
                    'cashier_id' => $cashier['id'],
                    'create_time >=' => $begin,
                    'create_time <=' => $end
                ])
                ->get_one('array');
        $total = array_map(function ($item) {
            return round($item, 2);
        }, $total);

        $fillPayModes = function ($result) use ($pay_modes) {
            $arr = arr_key($result, 'pay_mode');
            $arr1 = [];
            foreach ($pay_modes as $pay_mode) {
                $arr1[] = [
                    'pay_mode' => $pay_mode,
                    'num' => isset($arr[$pay_mode]) ? round($arr[$pay_mode]['num'], 2) : 0
                ];
            }

            return $arr1;
        };

        $income_groups = $money_log
            ->select('sum(case type when 0 then abs(num) when 1 then abs(num) else 0 end) as total_sale,
            sum(case type when 2 then abs(num) when 3 then abs(num) else 0 end) as total_recharge,
            sum(if(type=4, abs(num), 0)) as total_refund,
            sum(if(type=4, -abs(num), abs(num))) as num,
            pay_type as pay_mode')
            ->where([
                'cashier_id' => $cashier['id'],
                'create_time >=' => $begin,
                'create_time <=' => $end
            ])
            ->group_by('pay_type')
            ->get();
        $income_groups = $fillPayModes($income_groups);

        $sale_groups = $money_log
        ->select('sum(abs(num)) as num, pay_type as pay_mode')
        ->where([
            'cashier_id' => $cashier['id'],
            'create_time >=' => $begin,
            'create_time <=' => $end
        ])
        ->where_in('type', [0, 1])
        ->group_by('pay_type')
        ->get();
        $sale_groups = $fillPayModes($sale_groups);

        // 总会员充值
        $recharge = new Recharge();
        $recharge_groups = $recharge->select('sum(abs(recharge)) as num, pay_mode')
            ->where([
                'cashier_id' => $cashier['id'],
                'create_time >=' => $begin,
                'create_time <=' => $end
            ])
            ->group_by('pay_mode')
            ->get();
        $recharge_groups = $fillPayModes($recharge_groups);

        // 总退货
        $refund_groups = $money_log
        ->select('sum(abs(num)) as num, pay_type as pay_mode')
        ->where([
            'cashier_id' => $cashier['id'],
            'type' => 4,
            'create_time >=' => $begin,
            'create_time <=' => $end
        ])
        ->group_by('pay_type')
        ->get('array');
        $refund_groups = $fillPayModes($refund_groups);

        // 订单数量

        $order_counts = (new Order)
            ->where(
                [
                'cashier_id' => $cashier['id'],
                'status>' => 2,
                'status<' => 8,
                'create_time >=' => $begin,
                'create_time <=' => $end]
            )->counts();
        $order_vat = round((new Order)
            ->select('sum(vat) as vat')
            ->where(
                [
                'cashier_id' => $cashier['id'],
                'status>' => 2,
                'status<' => 8,
                'create_time >=' => $begin,
                'create_time <=' => $end]
            )->get_one()['vat'], 2);
        $item_counts = (new Order_item)
            ->join('orders', 'orders.id=order_items.order_id', 'left')
            ->select('sum(qty) as counts')
            ->where(
                [
                'orders.cashier_id' => $cashier['id'],
                'orders.status>' => 2,
                'orders.status<' => 8,
                'orders.create_time >=' => $begin,
                'orders.create_time <=' => $end]
            )->get_one('array')['counts'];

        //新增会员人数
        //新增会员金额
        $reg_money = round($money_log->select_sum('num')->where([
            'cashier_id' => $cashier['id'],
            'type' => 3,
            'create_time >=' => $begin,
            'create_time <=' => $end
        ])->get_one('array')['num']);
        $reg_user = User::where([
                'cashier_id' => $cashier['id'],
                'create_at >=' => $begin,
                'create_at <=' => $end
            ])->count();
        $xiaofei_user = $money_log->select('count(*) as num')
            ->where([
                'cashier_id' => $cashier['id'],
                'create_time >=' => $begin,
                'create_time <=' => $end,
                'user_id !=' => 0
            ])
            ->group_by('user_id')
            ->get_one()['num'];

        // 货损商品
        $refund_info = (new Refund)
            ->select('sum(if(is_damaged=1, qty, 0)) as huosun, sum(if(type=0, qty, 0)) as tuihuo')
            ->where([
                'cashier_id' => $cashier['id'],
                'create_time >=' => $begin,
                'create_time <=' => $end,
            ])
            ->get_one('array');
        $refund_info['huosun'] = round($refund_info['huosun'], 2);
        $refund_info['tuihuo'] = round($refund_info['tuihuo'], 2);

        // 收银员上班时间
        $log = $this->db->where('cashier_id', $this->session->cashier['id'])
                ->order_by('id desc')->get('cashier_login_logs')->row_array();

        $data = array_merge($total, [
                'cashier_id' => $cashier['id'],
                'cashier' => $this->session->cashier,
                'begin_time' => $log['login_time'],
                'end_time' => date('Y-m-d H:i:s'),
                'income_groups' => $income_groups,
                'sale_groups' => $sale_groups,
                'recharge_groups' => $recharge_groups,
                'refund_groups' => $refund_groups,
                'vat' => $order_vat,
                'order_counts' => $order_counts,
                'item_counts' => $item_counts,
                'reg_user' => $reg_user,
                'reg_money' => $reg_money,
                'xiaofei_user' => $xiaofei_user,
            ], $refund_info);
        if ($this->input->get('export')) {
            $this->export($data);
        } else {
            return $this->success($data);
        }
    }

    public function export($data)
    {
        $income_groups = arr_key($data['income_groups'], 'pay_mode');
        $sale_groups = arr_key($data['sale_groups'], 'pay_mode');
        $recharge_groups = arr_key($data['recharge_groups'], 'pay_mode');
        $refund_groups = arr_key($refund_groups, 'pay_mode');

        $objPHPExcel = new PHPExcel();
        // Set document properties
        $objPHPExcel->getProperties()
                             ->setTitle("统计excel")
                             ->setSubject("统计excel");
        // Add some data
        $objPHPExcel->setActiveSheetIndex(0)->getDefaultRowDimension()->setRowHeight(50);
        $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A6', "收银员: {$this->session->cashier['username']}")
                        ->mergeCells('C6:F6')
                        ->setCellValue('C6', "工作时间：{$data['begin_time']} - {$data['end_time']}")
                        ->mergeCells('B7:C7')
                        ->mergeCells('D7:E7')
                        ->mergeCells('F7:G7')
                        ->mergeCells('H7:I7')
                        ->setCellValue('B7', '实际总收入')
                        ->setCellValue('D7', '总销售额')
                        ->setCellValue('F7', '总会员充值')
                        ->setCellValue('H7', '总退货金额')
                        ->mergeCells('B8:C8')
                        ->mergeCells('D8:E8')
                        ->mergeCells('F8:G8')
                        ->mergeCells('H8:I8')
                        ->setCellValue('B8', $data['total_income'])
                        ->setCellValue('D8', $data['total_sale'])
                        ->setCellValue('F8', $data['total_recharge'])
                        ->setCellValue('H8', $data['total_refund']);
        $titles = [
                1 => '现金',
                6 => '银行卡',
                5 => 'PayM',
                4 => 'Paypal',
                0 => '会员卡'
            ];
        $i = 9;
        foreach ($titles as $key => $title) {
            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('B'.$i, $title.'总收入')
                    ->setCellValue('C'.$i, isset($income_groups[$key]) ? $income_groups[$key]['total'] : 0.00)
                    ->setCellValue('D'.$i, $title.'支付')
                    ->setCellValue('E'.$i, isset($sale_groups[$key]) ? $sale_groups[$key]['total'] : 0.00)
                    ->setCellValue('F'.$i, $title.'支付')
                    ->setCellValue('G'.$i, isset($recharge_groups[$key]) ? $recharge_groups[$key]['num'] : 0.00)
                    ->setCellValue('H'.$i, $title.'退款')
                    ->setCellValue('I'.$i, isset($refund_groups[$key]) ? $refund_groups[$key]['money'] : 0.00);
            $i++;
        }

        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('B15', 'VAT')
                ->setCellValue('C15', array_reduce($order_types, function ($car, $item) {
                    return $car + $item['vat'];
                }))
                ->setCellValue('D15', '订单数量')
                ->setCellValue('E15', $data['order_counts'])
                ->setCellValue('F15', '新增会员')
                ->setCellValue('G15', $data['reg_user'])
                ->setCellValue('H15', '货损商品')
                ->setCellValue('I15', '')
                ->setCellValue('D16', '商品数量')
                ->setCellValue('E16', $data['item_counts'])
                ->setCellValue('F16', '新增充值金额')
                ->setCellValue('G16', $data['reg_money'])
                ->setCellValue('H16', '退货商品')
                ->setCellValue('I16', '')
                ->setCellValue('F17', '会员消费人数')
                ->setCellValue('G17', $data['xiaofei_user']);

        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment; filename=tongji.xls");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        header("Expires: 0");

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }
}
